from peewee import *
import os

if os.path.exists('sampleDB.db'):
    os.remove('sampleDB.db')

db = SqliteDatabase("sampleDB.db")

class BaseModel(Model):
    class Meta:
        database = db

class Course(BaseModel):
    id = PrimaryKeyField()
    title = CharField(null=False)
    period = IntegerField()
    description = CharField()
    class Meta:
        order_by = ('title',)
        db_table = 'course'

class Teacher(BaseModel):
    id = PrimaryKeyField()
    name = CharField(null=False)
    gender = BooleanField()
    address = CharField()
    course_id = ForeignKeyField(Course,to_field="id",related_name="course")
    class Meta:
        order_by = ('name',)
        db_table = 'teacher'

Course.create_table()
Teacher.create_table()

Course.create(id = 1,title='经济学',period=320,description='文理科学生均可选修')
Course.create(id=2,title='大学英语',period=300,description='大一学生必修课')
Course.create(id=3,title='哲学',period=100,description='必修课')
Course.create(id=104,title='编译原理',period=100,description='计算机系选修课')

Teacher.create(name='白镇江',gender=True,address='桃花岛',course_id=1)
Teacher.create(name='李森',gender=True,address='侠客岛',course_id=3)
Teacher.create(name='张文文',gender=False,address='逍遥津',course_id=2)

record = Course.get(Course.title=='大学英语')
print("课程：%s,学时：%d"%(record.title,record.period))

record.period = 200
record.save()

record.delete_instance()

courses = Course.select()
for i in courses:
    print(i.id,i.period,i.description)

courses = Course.select().where(Course.id<10).order_by(Course.period.desc())
for i in courses:
    print(i.id,i.title,i.period,i.description)

total = Course.select(fn.Avg(Course.period).alias('avg_period'))
for i in total:
    print("平均学时：",i.avg_period)

Course.update(period=300).where(Course.id>100).execute()

Record = Course.select().join(Teacher).where(Teacher.gender==True)

for i in Record:
    print(i.id,i.title,i.period,i.description)

